﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SMIS.Entities;
using DX.CRC.Common.Entities;
using System.Data;

namespace SMIS.Dals
{
    class Dal4Staff : DalBase
    {
        public static List<Staff> SearchAllStaffs()
        {
            QueryObject qo = CreateQO();

            qo.CmdText = @"SELECT [SID] 
                      ,[SName] 
                      ,[DepName]
                      ,[JobName]
                      ,[Sex] as Sex_Field
                      ,[Birthday]
                      ,[Place]
                      ,[EducationBackground] as EducationBackground_Field
                      ,[Major]
                      ,[StartWorkingDate]
                      ,[DatesEmployed]
                      ,[Position]
                      ,[CreateTime]
                      ,[IsActive]
                      ,[Remark]
                  FROM [StaffInfo] where IsActive=1";

            return qo.xExecuteList<Staff>();
        }

        //【高月 2013-4-1-224841】配合部门筛选条件查询员工信息
        public static List<Staff> SearchSelectedDepStaffs(Staff staff)
        {
            QueryObject qo = CreateQO();

            qo.CmdText = @"SELECT [SID] 
                      ,[SName] 
                      ,[DepName]
                      ,[JobName]
                      ,[Sex] as Sex_Field
                      ,[Birthday]
                      ,[Place]
                      ,[EducationBackground] as EducationBackground_Field
                      ,[Major]
                      ,[StartWorkingDate]
                      ,[DatesEmployed]
                      ,[Position]
                      ,[CreateTime]
                      ,[IsActive]
                      ,[Remark]
                  FROM [StaffInfo] where IsActive=1 and DepName=@DepName";

            qo.AddParam("@DepName", staff.DepName);

            return qo.xExecuteList<Staff>();
        }

        public static List<Staff> InsertStaff(Staff staff)
        {
            QueryObject qo = CreateQO();

            qo.CmdText = @"INSERT INTO [StaffInfo]
                       ([SName]
                       ,[DepName]
                       ,[JobName]
                       ,[Sex]
                       ,[Birthday]
                       ,[Place]
                       ,[EducationBackground]
                       ,[Major]
                       ,[StartWorkingDate]
                       ,[DatesEmployed]
                       ,[Position]
                       ,[CreateTime]
                       ,[IsActive]
                       ,[Remark])
                 VALUES
                       (@SName
                       ,@DepName
                       ,@JobName
                       ,@Sex
                       ,@Birthday
                       ,@Place
                       ,@EducationBackground
                       ,@Major
                       ,@StartWorkingDate
                       ,@DatesEmployed
                       ,@Position
                       ,GETUTCDATE()
                       ,1
                       ,@Remark)";

            qo.AddParam("@SName", staff.SName);
            qo.AddParam("@DepName", staff.DepName);
            qo.AddParam("@JobName", staff.JobName);
            qo.AddParam("@Sex", staff.Sex);
            qo.AddParam("@Birthday", staff.Birthday);
            qo.AddParam("@Place", staff.Place);
            qo.AddParam("@EducationBackground", staff.EducationBackground);
            qo.AddParam("@Major", staff.Major);
            qo.AddParam("@StartWorkingDate", staff.StartWorkingDate);
            qo.AddParam("@DatesEmployed", staff.DatesEmployed);
            qo.AddParam("@Position", staff.Position);
            qo.AddParam("@Remark", staff.Remark);

            return qo.xExecuteList<Staff>();

        }

        public static List<Staff> UpdateSelectedStaff(Staff staff)
        {
            QueryObject qo = CreateQO();

            qo.CmdText = @"UPDATE [StaffInfo]
                           SET
                           [SName]=@SName
                           ,[DepName]=@DepName
                           ,[JobName]=@JobName
                           ,[Sex]=@Sex
                           ,[Birthday]=@Birthday
                           ,[Place]=@Place
                           ,[EducationBackground]=@EducationBackground
                           ,[Major]=@Major
                           ,[StartWorkingDate]=@StartWorkingDate
                           ,[DatesEmployed]=@DatesEmployed
                           ,[Position]=@Position
                           ,[Remark]=@Remark
                           WHERE SID=@SID";

            qo.AddParam("@SID", staff.SID);
            qo.AddParam("@SName", staff.SName);
            qo.AddParam("@DepName", staff.DepName);
            qo.AddParam("@JobName", staff.JobName);
            qo.AddParam("@Sex", staff.Sex);
            qo.AddParam("@Birthday", staff.Birthday);
            qo.AddParam("@Place", staff.Place);
            qo.AddParam("@EducationBackground", staff.EducationBackground);
            qo.AddParam("@Major", staff.Major);
            qo.AddParam("@StartWorkingDate", staff.StartWorkingDate);
            qo.AddParam("@DatesEmployed", staff.DatesEmployed);
            qo.AddParam("@Position", staff.Position);
            qo.AddParam("@Remark", staff.Remark);

            return qo.xExecuteList<Staff>();
        }

        public static bool DeleteSelectedStaff(Staff staff)
        {
            QueryObject qo = CreateQO();
            qo.CmdText = @"declare @Count int
                           select @Count=COUNT(*) from StaffInfo S 
                           where @SName in (select I.Staff from IncomeInfo I)
                           if @Count=0
                           begin
                           UPDATE [StaffInfo]
                           SET [IsActive] = 0
                           WHERE SID=@SID
                           end";

            qo.AddParam("@SName", staff.SName);
            qo.AddParam("@SID", staff.SID);

            return qo.xExecuteNonQuery() == 1;
        }

        public static DataTable GetJobs()
        {
            QueryObject qo = CreateQO();
            qo.CmdText = @"SELECT [JobID]
                          ,[JobName]
                      FROM [JobInfo] where IsActive=1";

            return qo.xExecuteDataTable();
        }

        public static DataTable GetDeparts()
        {
            QueryObject qo = CreateQO();
            qo.CmdText = @"SELECT [DID]
                    ,[DName]
                FROM [DepartInfo] where IsActive=1";

            return qo.xExecuteDataTable();
        }
    }
}
